/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package edu.uoc.tdp.dal;

import edu.uoc.tdp.entity.Resource;
import edu.uoc.tdp.entity.SpaceResource;
import edu.uoc.tdp.customException.EIssueException;
import edu.uoc.tdp.i18n.*;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.swing.JOptionPane;
  
/**
 * @author J2RS / Jordi Sanchez
 */
public class ManagementSpacesResources {

   private ManagementDb mngBD = null;
   private Connection conexion = null;

    public ManagementSpacesResources() throws EIssueException{
        try {
            mngBD = new ManagementDb();
            mngBD.connectDB();
            conexion = mngBD.getConnection();
        } catch (edu.uoc.tdp.common.EIssueException ex) {
            Logger.getLogger(ManagementSpacesResources.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public ManagementSpacesResources(ManagementDb mngDb) {
        mngBD = mngDb;
        conexion = mngDb.getConnection();
    }

    /**
     * Mètode que obte la lista de espais - taula spaces_resources
     * @return Lista de espais
     */
    public List<SpaceResource> GetAllSpacesResources() throws EIssueException{
        List<SpaceResource> listSpacesResources = new ArrayList();

       try {
            Statement command = this.mngBD.createCommand();
            String select = "SELECT spaceid, resourceid, record_date  FROM spaces_resources";
            ResultSet rs = command.executeQuery(select);
            while (rs.next()) {
                listSpacesResources.add(new SpaceResource(rs.getInt(1), rs.getInt(3),rs.getDate(3) ));
                // El primer index en rs es el 1
            }
            rs.close();
            command.close();
        } catch (SQLException sqle) {
            System.out.println(sqle.getMessage());
            throw new EIssueException(TDPLanguageUtils.getMessage
                    (TDPLanguageConstants.ERR_SQL));
        } catch (Exception e) {
            System.out.println(e.getMessage());
            throw new EIssueException(TDPLanguageUtils.getMessage
                    (TDPLanguageConstants.ERR_UNKNOW));
        }
        return listSpacesResources;
    }

    
    /**
     * Mètode que obte tots els recursos d'un espai - taula spaces_resources
     * @return Lista de recursos d'un espai
     */
    public List<Resource> GetOneSpaceResources(int spaceid) throws EIssueException {
        List<Resource> listResources = new ArrayList();
        
        try {
            Statement command = this.mngBD.createCommand();
            String select = "SELECT SR.spaceid, SR.resourceid, R.resourcedescription, SR.record_date  FROM spaces_resources as SR  inner join all_resources as R on R.resourceid = SR.resourceid WHERE spaceid="+String.valueOf(spaceid) +" ORDER BY SR.resourceid";
            //command.setInt(1,spaceid);
            ResultSet rs = command.executeQuery(select);
            while (rs.next()) {
                listResources.add(new Resource(rs.getInt(1), rs.getString(2), rs.getInt(3),rs.getDouble(4),rs.getDouble(5),rs.getDate(6),rs.getString(7),rs.getDate(8),rs.getString(9),rs.getDate(10) ));
                // El primer index en rs es el 1
            }
            rs.close();
            command.close();

        } catch (SQLException sqle) {
            System.out.println(sqle.getMessage());
            throw new EIssueException(TDPLanguageUtils.getMessage
                    (TDPLanguageConstants.ERR_STOP));
        } catch (Exception e) {
            System.out.println(e.getMessage());
            throw new EIssueException(TDPLanguageUtils.getMessage
                    (TDPLanguageConstants.ERR_UNKNOW));
        }
        
        return listResources;
    }
    
    
    
    /**
     * Mètode que guarda dades (Insert) un recurs dins d'una aula - taula spaces_resources
     * @return boolea
     */    
    public boolean SaveSpaceResource(int SpaceId, int ResourceId) throws EIssueException{
        boolean PROCESS_STATE = false;
        try {
            String insertQuery = "INSERT INTO spaces_resources(spaceid, resourceid, record_date) VALUES (?, ?, now())";
            //JOptionPane.showMessageDialog(this, insertQuery, "sql", JOptionPane.ERROR_MESSAGE);
            
            PreparedStatement command = this.mngBD.prepareCommand(insertQuery);
            command.setInt(1, SpaceId);
            command.setInt(2, ResourceId);
            command.execute();
            command.close();
            PROCESS_STATE = true;
        } catch (SQLException sqle) {
            System.out.println(sqle.getMessage());
            throw new EIssueException(TDPLanguageUtils.getMessage
                    (TDPLanguageConstants.ERR_SQL));
        } catch (Exception e) {
            System.out.println(e.getMessage());
            throw new EIssueException(TDPLanguageUtils.getMessage
                    (TDPLanguageConstants.ERR_UNKNOW));
        }
        
        return PROCESS_STATE;
    }

    
    /**
     * Mètode que guarda dades (Insert) un recurs dins d'una aula - taula spaces_resources
     * @return boolea
     */    
    public boolean DeleteSpaceResource(int spaceid, int resourceId) throws EIssueException{
        boolean PROCESS_STATE = false;
        try {
            String DeleteQuery = "DELETE FROM spaces_resources WHERE spaceid=? and resourceid=?;";
            PreparedStatement command = this.mngBD.prepareCommand(DeleteQuery);
            command.setInt(1, spaceid);       
            command.setInt(2, resourceId);       
            command.execute();
            command.close();
            PROCESS_STATE = true;
        } catch (SQLException sqle) {
            System.out.println(sqle.getMessage());
            throw new EIssueException(TDPLanguageUtils.getMessage
                    (TDPLanguageConstants.ERR_SQL));
        } catch (Exception e) {
            System.out.println(e.getMessage());
            throw new EIssueException(TDPLanguageUtils.getMessage
                    (TDPLanguageConstants.ERR_UNKNOW));
        }
        
        return PROCESS_STATE;
    }
    
    /**
     * Mètode que obte la lista de recursos d'un espai
     * @return Lista de espais
     * autor tomeu
     */
    public List<Resource> GetAllResourcesFromSpace(Integer spaceid) throws EIssueException{
        List<Resource> listSpacesResources = new ArrayList();

       try {
            Statement command = this.mngBD.createCommand();
            //en aquest tipus de metodes es on s'agreix haver fet una relacio a les entity amb les altres clases
            String select = "SELECT all_resources.*  FROM all_resources INNER JOIN spaces_resources ON all_resources.resourceid = spaces_resources.resourceid WHERE spaceid=" + String.valueOf(spaceid);
            ResultSet rs = command.executeQuery(select);
            while (rs.next()) {
                listSpacesResources.add(new Resource(rs.getInt(1), rs.getString(2), rs.getInt(3),rs.getDouble(4),rs.getDouble(5),rs.getDate(6),rs.getString(7),rs.getDate(8),rs.getString(9),rs.getDate(10)));
                // El primer index en rs es el 1
            }
            rs.close();
            command.close();
        } catch (SQLException sqle) {
            System.out.println(sqle.getMessage());
            throw new EIssueException(TDPLanguageUtils.getMessage
                    (TDPLanguageConstants.ERR_SQL));
        } catch (Exception e) {
            System.out.println(e.getMessage());
            throw new EIssueException(TDPLanguageUtils.getMessage
                    (TDPLanguageConstants.ERR_UNKNOW));
        }
        return listSpacesResources;
    }
    
}
